/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package functions;

import java.sql.ResultSet;
import java.sql.*;
import java.util.ArrayList;
import model.Catalog;
import model.Database;
import model.Distributor;
import model.Drug;
import model.Item;
import model.Sklad;
import model.Sklad_drugs;

/**
 *
 * @author JArik
 */
public class Drugs extends Items{



    private String query;
    private boolean groupByName,
                    groupByDate,
                    groupByDist;
    public Drugs()
    {
        query = " SD.type != 'muud' AND SD.currentAmount>0 ";
        groupByName = false;
        groupByDate = false;
        groupByDist =false;
    }
    public void searchByName(String name)
    {
        query += "AND SD.name LIKE '%"+name+"%' ";
    }
    public void searchByDist(String dist)
    {
        query += "AND DI.name LIKE '%"+dist+"%' ";
    }
    public void searchByStrictType(String type)
    {
        query += "AND SD.type = '"+type +"' ";
    }
    public void searchBySoftType(String type)
    {
        query += "AND SD.type = '"+type +"' OR SD.type ='' ";
    }
    public void searchByDates(java.util.Date date1, java.util.Date date2)
    {
        String date = (date1.getYear()+1900) + "-" + (date1.getMonth()+1) + "-"
                      + (date1.getDate());
        String sdate = (date2.getYear()+1900) + "-" + (date2.getMonth()+1) + "-"
                      + (date2.getDate());
        query += " AND date BETWEEN '" + date + "' AND '" + sdate +"' ";
    }
    public void groupByName()
    {
        groupByName=true;
    }

    @Override
    public ArrayList search() throws SQLException
    {
        Database db = new Database();
        if(groupByName)
        {
            query +=" GROUP BY SD.name ";
        }
        else
        {
            query +=" GROUP BY SD.rec_id ";
        }
        query += " ORDER BY SD.type DESC ";
        ResultSet set = db.getDBInfo(
                "SD.id, SD.distrId, SD.name, SD.date, SD.descr, " +
                "SD.sell, SUM(SD.currentAmount) AS amount, DI.name AS distName, SD.type,SD.buy,SD.rec_id ",
"(SELECT SD.id, SD.date, SD.sell, SD.buy, SD.currentAmount, SD.descr, D.name, D.description, SD.distrId, D.type,SD.rec_id "+
"FROM sklad_drugs SD LEFT JOIN drugs D ON SD.id = D.id) SD " +
"LEFT JOIN distributors DI ON DI.id = SD.distrId ",
                query);
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug(set.getInt(1),     // id
                              set.getInt(2),  // dist
                              set.getString(3),  // name
                              set.getDate(4)     // date
                              );

            drug.setDescr(set.getString(5));
            drug.setDistributor(new Distributor(set.getInt(2), set.getString(8)));
            drug.setSellPrice(set.getDouble(6));
            drug.setAmount(set.getDouble(7));
            drug.setType(set.getString(9));
            drug.setBuyPrice(set.getDouble(10));
            list.add(drug);
        }
        db.closeStatement();
        db = null;
        return list;
    }
    public static ArrayList<Drug> getAll() throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("*", "drugs","deleted=0");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug dr = new Drug();
            dr.setId(set.getInt("id"));
            dr.setName(set.getString("name"));
            dr.setDescr(set.getString("description"));
            dr.setType(set.getString("type"));
            dr.setIzmType(set.getString("izm"));
            list.add(dr);
        }
        db.closeStatement();
        db = null;
        return list;
    }
    public static ArrayList<Drug> getByName(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("*", "drugs",
                "CONCAT(type,' ',name) LIKE '%"+name+"%' AND deleted=0 " +
                " ORDER BY CONCAT(type,' ',name) != '"+name+"'," +
                "         CONCAT(type,' ',name) not like '"+name+" %'," +
                "         CONCAT(type,' ',name) not like '"+name+"%', name  ");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug(set.getInt("id"),     // id
                              set.getString("name")  // name
                              );
            drug.setDescr(set.getString("description"));
            drug.setType(set.getString("type"));
            drug.setIzmType(set.getString("izm"));
            list.add(drug);

        }
        db.closeStatement();
        db = null;
        return list;
    }
    public static ArrayList<Drug> getByNameAndType(String name, String type) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo(
                "*", "drugs","name LIKE '%"+name+"%' AND type = '"+type+"' AND deleted=0 ORDER BY type DESC ");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug(set.getInt(1),     // id
                              set.getString(2)  // name
                              );
            drug.setDescr(set.getString(3));
            drug.setType(set.getString(4));
            list.add(drug);

        }
        db.closeStatement();
        db = null;
        return list;
    }
    public static ArrayList<Drug> getFromSkladByName(String name) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("SD.id, SD.distrId, SD.name, SD.date, SD.descr, " +
                                           "SD.sell, SD.amount, DI.name AS distName, SD.type, SD.buy ",
"(SELECT SD.id, SD.date, SD.sell, SD.buy, SD.amount, SD.descr, D.name, D.description, SD.distrId, D.type "+
"FROM sklad_drugs SD LEFT JOIN drugs D ON SD.id = D.id) SD " +
"LEFT JOIN distributors DI ON DI.id = SD.distrId",
                "SD.name = "+name+"");
        ArrayList<Drug> list = new ArrayList<Drug>();
        while ( set.next())
        {
            Drug drug = new Drug(set.getInt(1),     // id
                              set.getInt(2),  // dist
                              set.getString(3),  // name
                              set.getDate(4)     // date
                              );

            drug.setDescr(set.getString(5));
            drug.setDistributor(new Distributor(set.getInt(2), set.getString(8)));
            drug.setSellPrice(set.getDouble(6));
            drug.setAmount(set.getInt(7));
            drug.setType(set.getString(9));
            drug.setBuyPrice(set.getDouble(10));
            list.add(drug);
        }
        db.closeStatement();
        db = null;
        return list;
    }
    public static ArrayList<String> getTypes() throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("drugs.type", "drugs", "1=1 GROUP BY drugs.type");
        ArrayList<String> res = new ArrayList<String>();
        while(set.next())
        {
            if(set.getString(1) != null && !set.getString(1).equals(""))
            res.add(set.getString(1));
        }
        db.closeStatement();
        return res;
    }
    
    public static Drug getById(int id) throws SQLException
    {
        Database db = new Database();
        Drug drug=null;
        ResultSet set = db.getDBInfo("*", "drugs", "id = "+id);
        if(set.next())
        drug =  new Drug(set.getInt(1), set.getInt(2),
                       set.getString(3), set.getDate(4));
        db.closeStatement();
        db = null;
        return drug;
    }
    public static int addToCatalog(Drug drug) throws SQLException
    {
        String values="", what="";
        if(drug.getName() !=null)
        {
            what += "name";
            values += "'"+drug.getName()+"'";
        } else throw new SQLException("Введите название лекарства");
        if(drug.getType() != null)
        {
            what +=",type";
            values += ",'"+drug.getType()+"'";
        }
        if(drug.getDescr() != null)
        {
            what +=",description";
            values +=",'"+drug.getDescr() +"'";
        }
        return Catalog.addDrug(what, values);
    }

    public static int  addToSklad(Drug drug, double currentAmount,String currency, int skl, double tax) throws SQLException
    {
        return Items.addToSklad(drug, currentAmount, currency, skl, tax);
    }
    public static int getByNameTypeAmountdistrIdSkl(String name, Date inDate,double currentAmount,
                                                          double incomeAmount, int distrId,
                                                          int skladId) throws SQLException {
        Database db = new Database();
        if(inDate != null)
        inDate = new Date(inDate.getTime());
        ResultSet set = db.getDBInfo(
                "SD.id ",
                " sklad_drugs SD LEFT JOIN drugs D ON SD.id = D.id ",
                " D.name LIKE '%"+name+"%' " +
                " AND currentAmount='"+currentAmount+"' AND incomeAmount='"+incomeAmount+"' " +
                " AND distrId="+distrId+" AND skl="+skladId+" AND date='"+inDate+"' ");
        int id=0;
        if ( set.next())
        {
            id = set.getInt(1);
        }
        db.closeStatement();
        return id;
    }
    public static int addDrug(int visId, int skladId, int dogId, int docId, double drugPrice, Date vizitDate, String string, double payed, double drugAmount) throws SQLException {
        return Items.dischargeItem(visId, skladId, dogId, docId, drugPrice,
                                   vizitDate, string, payed, drugAmount);
    }
    public static int recordAsDischargedDrug(int visId, int skladId, int dogId, int docId, double drugPrice, Date vizitDate, String string, double drugPrice0, double drugAmount) throws SQLException {
        return Items.recordAsDischargedItem(visId, skladId, dogId, docId, drugPrice,
                                   vizitDate, string, drugPrice0, drugAmount);
    }


    public static int updateDisDrug(int disId,
                                      int visId, int skladId, int dogId,
                                      int docId, double drugPrice,
                                      Date vizitDate, String descr,
                                      double payed, double drugAmount,
                                      double oldAmount,
                                      boolean deleted)
                                      throws SQLException
    {
        if(disId <= 0)
            throw new SQLException("Неправельный идентификатор.");
        if(visId <= 0)
          throw new SQLException("Неправельный номер визита: " + visId);
        if(drugPrice < 0)
          throw new SQLException("Неуказана цена продажи: " + drugPrice);
        if(dogId == 0)
          throw new SQLException("Неуказано животное");
        if(skladId < 0)
          throw new SQLException("Товара нет на складе");
        if(docId == 0)
          throw new SQLException("Неуказан врач");
        if(vizitDate == null)
            throw new SQLException("Неправельная дата выдачи: " + vizitDate);
        if( payed < 0)
          throw new SQLException("Неправельная цена за товар: " + payed);
        if( drugAmount < 0)
            throw new SQLException("Неправельное количество товара: " + drugAmount);

        Sklad_drugs.updateDrugAmount(skladId, drugAmount, oldAmount);
        vizitDate = new Date(vizitDate.getTime());
        Database db = new Database();
        int res = db.updateDBInfo("discharged_drugs",
                "visitId="+visId+","+
                "dogId="+dogId+","+
                "drugId="+skladId+","+
                "exec="+docId+","+
                "descr='"+descr+"',"+
                "date='"+vizitDate+"',"+
                "sellPrice="+drugPrice+","+
                "amount="+drugAmount+","+
                "payed="+payed+","+
                "deleted="+(deleted == true ? "1":"0"),
                " disId="+disId);
        db.closeStatement();
        db=null;
        return res;
        
    }
    public static int updateSklad(Item item, double currAm, String currency, int skladId, double tax) throws SQLException
    {
        Database db = new Database();
        if(item.getInDate() == null)
            throw new SQLException("Укажите дату ввоза товара.");
        int res = db.updateDBInfo("sklad_drugs",
                                  "id="+item.getId()+","+
                                  "buy="+item.getBuyPrice()+","+
                                  "sell="+item.getSellPrices()[0]+","+
                                  "sellMedium="+item.getSellPrices()[1]+","+
                                  "sellHigh="+item.getSellPrices()[2]+","+
                                  "currency='"+currency+"',"+
                                  "tax="+tax+","+
                                  "date='" +item.getInSQLDate() + "',"+
                                  "currentAmount="+currAm+","+
                                  "descr='"+item.getDescr()+"',"+
                                  (item.getDist() != null ? "distrId="+item.getDist().getId()+",":"")+
                                  "skl="+skladId
                                  ,
                                  "rec_id="+item.getRecId());
        db.closeStatement();
        db=null;
        return res;
    }
}

/*
            drug.setType(set.getString(2));
            drug.setName(set.getString(3));
            drug.setSellPrices(set.getDouble(4), set.getDouble(5), set.getDouble(6));
            drug.setAmount(set.getDouble(7));
            drug.setInDate(set.getDate(8));
            drug.setSkladId(set.getInt(9));
            list.add(drug);

        }
        db.closeStatement();
        return list;
    }
}
*/